﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using FOModels.BlogNews;
namespace DataAccess.BlogNews
{
    public class BlogNewsDAL
    {
        private SQLDataAccess db;
        public BlogNewsDAL()
        {
            db = new SQLDataAccess();
        }
        //  public DataTable getBlogNewsByCate(int CateID, int langid)
        //   {
        //       return db.exeSelect("select bl.BlogNewsCategoryName,b.BlogNewsCategoryID, bn.BlogNewsHot,bn.BlogNewsID,bn.BlogNewsImg,bn.CreateDate,bn.CreateUser,bn.UpdateDate,bn.UpdateUser,bn.Enable,bnl.BlogNewsTitle,bn.ViewCount, bnl.BlogNewsSumary ,bnl.BlogNewsContent from BlogNewsCategory b join BlogNewsCategoryLang bl on b.BlogNewsCategoryID = bl.BlogNewsCategoryID join BlogNews bn on bl.BlogNewsCategoryID = bn.BlogNewsCategoryID join BlogNewsLang bnl on bn.BlogNewsID = bnl.BlogNewsID where bl.LangID = " + langid + " and bnl.LangID = " + langid + " and bn.Enable = 1 and b.BlogNewsCategoryID = " + CateID + " order by bn.BlogNewsID desc");
        //   }
        public DataTable getTOp1BlogNewsByID(int langid)
        {
            return db.exeSelect("select TOP 1 bcl.BlogNewsCategoryName,bc.BlogNewsCategoryID, n.BlogNewsHot,n.BlogNewsID,n.BlogNewsImg,n.CreateDate,n.CreateUser,n.UpdateDate,n.UpdateUser,n.Enable,nl.BlogNewsTitle,n.ViewCount, nl.BlogNewsSumary ,nl.BlogNewsContent  from BlogNews n join BlogNewsLang nl on n.BlogNewsID = nl.BlogNewsID join BlogNewsCategory bc on n.BlogNewsCategoryID = bc.BlogNewsCategoryID join BlogNewsCategoryLang bcl on bc.BlogNewsCategoryID = bcl.BlogNewsCategoryID where n.Enable = 1 and nl.LangID = " + langid + " and bcl.LangID = " + langid + " order by n.BlogNewsID desc");
        }
        public DataTable getTOp3BlogNewsByIDNotID(int langid, int BlogNewsID)
        {
            return db.exeSelect("select TOP 3 bcl.BlogNewsCategoryName,bc.BlogNewsCategoryID, n.BlogNewsHot,n.BlogNewsID,n.BlogNewsImg,n.CreateDate,n.CreateUser,n.UpdateDate,n.UpdateUser,n.Enable,nl.BlogNewsTitle,n.ViewCount, nl.BlogNewsSumary ,nl.BlogNewsContent  from BlogNews n join BlogNewsLang nl on n.BlogNewsID = nl.BlogNewsID join BlogNewsCategory bc on n.BlogNewsCategoryID = bc.BlogNewsCategoryID join BlogNewsCategoryLang bcl on bc.BlogNewsCategoryID = bcl.BlogNewsCategoryID where n.Enable = 1 and nl.LangID = " + langid + " and bcl.LangID = " + langid + "and n.BlogNewsID != " + BlogNewsID + "  order by n.BlogNewsID desc");
        }
        public DataTable getTOpBlogNewsByID(int langid, int top)
        {
            return db.exeSelect("select TOP " + top + " bcl.BlogNewsCategoryName,bc.BlogNewsCategoryID, n.BlogNewsHot,n.BlogNewsID,n.BlogNewsImg,n.CreateDate,n.CreateUser,n.UpdateDate,n.UpdateUser,n.Enable,nl.BlogNewsTitle,n.ViewCount, nl.BlogNewsSumary ,nl.BlogNewsContent  from BlogNews n join BlogNewsLang nl on n.BlogNewsID = nl.BlogNewsID join BlogNewsCategory bc on n.BlogNewsCategoryID = bc.BlogNewsCategoryID join BlogNewsCategoryLang bcl on bc.BlogNewsCategoryID = bcl.BlogNewsCategoryID where n.Enable = 1 and nl.LangID = " + langid + " and bcl.LangID = " + langid + " order by n.BlogNewsID desc");
        }
        public DataTable getTOpBlogNewsByBlogNewsHot(int langid, int top)
        {
            return db.exeSelect("select TOP " + top + " bcl.BlogNewsCategoryName,bc.BlogNewsCategoryID, n.BlogNewsHot,n.BlogNewsID,n.BlogNewsImg,n.CreateDate,n.CreateUser,n.UpdateDate,n.UpdateUser,n.Enable,nl.BlogNewsTitle,n.ViewCount, nl.BlogNewsSumary ,nl.BlogNewsContent  from BlogNews n join BlogNewsLang nl on n.BlogNewsID = nl.BlogNewsID join BlogNewsCategory bc on n.BlogNewsCategoryID = bc.BlogNewsCategoryID join BlogNewsCategoryLang bcl on bc.BlogNewsCategoryID = bcl.BlogNewsCategoryID where n.Enable = 1 and nl.LangID = " + langid + " and bcl.LangID = " + langid + " and n.BlogNewsHot = 1 order by n.BlogNewsID desc");
        }
        public DataTable getTOpBlogNewsByBlogNewsHotNotBlogNewsID(int BlogNewsID, int langid, int top)
        {
            return db.exeSelect("select TOP " + top + " bcl.BlogNewsCategoryName,bc.BlogNewsCategoryID, n.BlogNewsHot,n.BlogNewsID,n.BlogNewsImg,n.CreateDate,n.CreateUser,n.UpdateDate,n.UpdateUser,n.Enable,nl.BlogNewsTitle,n.ViewCount, nl.BlogNewsSumary ,nl.BlogNewsContent  from BlogNews n join BlogNewsLang nl on n.BlogNewsID = nl.BlogNewsID join BlogNewsCategory bc on n.BlogNewsCategoryID = bc.BlogNewsCategoryID join BlogNewsCategoryLang bcl on bc.BlogNewsCategoryID = bcl.BlogNewsCategoryID where n.Enable = 1 and nl.LangID = " + langid + " and bcl.LangID = " + langid + " and n.BlogNewsHot = 1 and n.BlogNewsID !=" + BlogNewsID + " order by n.BlogNewsID desc");
        }
        // Lấy blog news theo id để hiển thị ra ngoài
        public DataTable getBlogNewsByIDToGet(int BlogNewsID, int langid)
        {
            return db.exeSelect("select bcl.BlogNewsCategoryName,bc.BlogNewsCategoryID, n.BlogNewsHot,n.BlogNewsID,n.BlogNewsImg,n.CreateDate,n.CreateUser,n.UpdateDate,n.UpdateUser,n.Enable,nl.BlogNewsTitle,n.ViewCount, nl.BlogNewsSumary ,nl.BlogNewsContent  from BlogNews n join BlogNewsLang nl on n.BlogNewsID = nl.BlogNewsID join BlogNewsCategory bc on n.BlogNewsCategoryID = bc.BlogNewsCategoryID join BlogNewsCategoryLang bcl on bc.BlogNewsCategoryID = bcl.BlogNewsCategoryID where n.Enable = 1 and nl.LangID = " + langid + " and bcl.LangID = " + langid + " and n.BlogNewsID = " + BlogNewsID + "");
        }
        // Lấy blog news theo id để hiển thị ra ngoài
        public DataTable getBlogNewsByParentIDNotBlogNewsID(int TOP, int ParentID, int langid, int BlogNewsID)
        {
            return db.exeSelect("Select TOP " + TOP + " bcl.BlogNewsCategoryName,bc.BlogNewsCategoryID, b.BlogNewsHot,b.BlogNewsID,b.BlogNewsImg,b.CreateDate,b.CreateUser,b.UpdateDate,b.UpdateUser,b.Enable,bl.BlogNewsTitle,b.ViewCount, bl.BlogNewsSumary ,bl.BlogNewsContent from BlogNews b join BlogNewsLang bl on b.BlogNewsID = bl.BlogNewsID join BlogNewsCategory bc on b.BlogNewsCategoryID = bc.BlogNewsCategoryID join BlogNewsCategoryLang bcl on bcl.BlogNewsCategoryID = bc.BlogNewsCategoryID where bc.ParentID = " + ParentID + " and B.Enable = 1 and bc.Enable = 1 and bl.LangID = " + langid + " and bcl.LangID = " + langid + " and b.BlogNewsID != " + BlogNewsID + " order by b.BlogNewsID");
        }
        public DataTable getBlogNewsByParentID(int TOP, int ParentID, int langid)
        {
            return db.exeSelect("Select TOP " + TOP + " bcl.BlogNewsCategoryName,bc.BlogNewsCategoryID, b.BlogNewsHot,b.BlogNewsID,b.BlogNewsImg,b.CreateDate,b.CreateUser,b.UpdateDate,b.UpdateUser,b.Enable,bl.BlogNewsTitle,b.ViewCount, bl.BlogNewsSumary ,bl.BlogNewsContent from BlogNews b join BlogNewsLang bl on b.BlogNewsID = bl.BlogNewsID join BlogNewsCategory bc on b.BlogNewsCategoryID = bc.BlogNewsCategoryID join BlogNewsCategoryLang bcl on bcl.BlogNewsCategoryID = bc.BlogNewsCategoryID where bc.ParentID = " + ParentID + " and B.Enable = 1 and bc.Enable = 1 and bl.LangID = " + langid + " and bcl.LangID = " + langid + " order by b.BlogNewsID");
        }
        // Lấy thông tin blog news để quản lý
        public DataTable getBlogNewsByID(int BlogNewsID, int langid)
        {
            return db.exeSelect("select bcl.BlogNewsCategoryName,bc.BlogNewsCategoryID, n.BlogNewsHot,n.BlogNewsID,n.BlogNewsImg,n.CreateDate,n.CreateUser,n.UpdateDate,n.UpdateUser,n.Enable,nl.BlogNewsTitle,n.ViewCount, nl.BlogNewsSumary ,nl.BlogNewsContent from BlogNews n join BlogNewsLang nl on n.BlogNewsID = nl.BlogNewsID join BlogNewsCategory bc on n.BlogNewsCategoryID = bc.BlogNewsCategoryID join BlogNewsCategoryLang bcl on bc.BlogNewsCategoryID = bcl.BlogNewsCategoryID where n.BlogNewsID = " + BlogNewsID + " and bcl.LangID = " + langid + " and nl.LangID = " + langid + "");
        }
        // Lấy top 1 blog news moi nhat
        public DataTable gettop1BlogNews()
        {
            return db.exeSelect("Select Top 1 BlogNewsID from BlogNews order by BlogNewsID desc");
        }
        public int UpdateBlogNewsViewCount(int ViewCount, int BlogNewsID)
        {
            return db.exeUpdate("update BlogNews set ViewCount=" + ViewCount + "  where BlogNewsID = " + BlogNewsID + "");
        }
        // update blog news
        public int UpdateBlogNews(BlogNewsModels models)
        {
            return db.exeUpdate("update BlogNews set Enable = '" + models.Enable + "' , BlogNewsImg = '" + models.BlogNewsImg + "', UpdateDate=getdate(),UpdateUser ='" + models.UpdateUser + "',BlogNewsHot = '" + models.BlogNewsHot + "',BlogNewsCategoryID = " + models.BlogNewsCategoryID + " where BlogNewsID = " + models.BlogNewsID + "");
        }
        public int UpdateBlogNewsLang(BlogNewsModels models, int langid)
        {
            return db.exeUpdate("update BlogNewsLang set BlogNewsTitle = N'" + models.BlogNewsTitle + "', BlogNewsSumary = N'" + models.BlogNewsSumary + "',BlogNewsContent=N'" + models.BlogNewsContent + "' where BlogNewsID = " + models.BlogNewsID + " and LangID=" + langid + "");
        }

        // thêm blog news
        public int InsertBlogNews(BlogNewsModels models)
        {
            return db.exeUpdate("insert into BlogNews(BlogNewsCategoryID,CreateUser,BlogNewsImg,BlogNewsHot) values (" + models.BlogNewsCategoryID + ",N'" + models.CreateUser + "','" + models.BlogNewsImg + "','" + models.BlogNewsHot + "')");
        }
        public int InsertBlogNewsLang(BlogNewsModels models, int langid)
        {
            return db.exeUpdate("insert into BlogNewsLang values (" + models.BlogNewsID + "," + langid + ",N'" + models.BlogNewsTitle + "',N'" + models.BlogNewsSumary + "',N'" + models.BlogNewsContent + "')");
        }

        // danh sach blog new by cate
        public DataTable getBlogNewByCateNotID(int BlogNewsCategoryID, int langid, int BlogNewsID)
        {
            return db.exeSelect("select bcl.BlogNewsCategoryName,bc.BlogNewsCategoryID, n.BlogNewsHot,n.BlogNewsID,n.BlogNewsImg,n.CreateDate,n.CreateUser,n.UpdateDate,n.UpdateUser,n.Enable,nl.BlogNewsTitle,n.ViewCount, nl.BlogNewsSumary ,nl.BlogNewsContent from BlogNews n join BlogNewsLang nl on n.BlogNewsID = nl.BlogNewsID join BlogNewsCategory bc on n.BlogNewsCategoryID = bc.BlogNewsCategoryID join BlogNewsCategoryLang bcl on bc.BlogNewsCategoryID = bcl.BlogNewsCategoryID where n.BlogNewsID != " + BlogNewsID + " and n.BlogNewsCategoryID = " + BlogNewsCategoryID + " and bcl.LangID = " + langid + " and nl.LangID = " + langid + " order by n.BlogNewsID desc");
        }
        // danh sach blog new by cate
        public DataTable getBlogNewByCate(int BlogNewsCategoryID, int langid)
        {
            return db.exeSelect("select bcl.BlogNewsCategoryName,bc.BlogNewsCategoryID, n.BlogNewsHot,n.BlogNewsID,n.BlogNewsImg,n.CreateDate,n.CreateUser,n.UpdateDate,n.UpdateUser,n.Enable,nl.BlogNewsTitle,n.ViewCount, nl.BlogNewsSumary ,nl.BlogNewsContent from BlogNews n join BlogNewsLang nl on n.BlogNewsID = nl.BlogNewsID join BlogNewsCategory bc on n.BlogNewsCategoryID = bc.BlogNewsCategoryID join BlogNewsCategoryLang bcl on bc.BlogNewsCategoryID = bcl.BlogNewsCategoryID where  n.BlogNewsCategoryID = " + BlogNewsCategoryID + " and bcl.LangID = " + langid + " and nl.LangID = " + langid + " order by n.BlogNewsID desc");
        }
        public DataTable getTOP1BlogNewByCate(int BlogNewsCategoryID, int langid)
        {
            return db.exeSelect("select TOP 1 bcl.BlogNewsCategoryName,bc.BlogNewsCategoryID, n.BlogNewsHot,n.BlogNewsID,n.BlogNewsImg,n.CreateDate,n.CreateUser,n.UpdateDate,n.UpdateUser,n.Enable,nl.BlogNewsTitle,n.ViewCount, nl.BlogNewsSumary ,nl.BlogNewsContent from BlogNews n join BlogNewsLang nl on n.BlogNewsID = nl.BlogNewsID join BlogNewsCategory bc on n.BlogNewsCategoryID = bc.BlogNewsCategoryID join BlogNewsCategoryLang bcl on bc.BlogNewsCategoryID = bcl.BlogNewsCategoryID where n.BlogNewsCategoryID = " + BlogNewsCategoryID + " and bcl.LangID = " + langid + " and nl.LangID = " + langid + "order by n.BlogNewsID desc");
        }
        // lay danh sach blog news
        public DataTable getall(int langid)
        {
            return db.exeSelect("select bcl.BlogNewsCategoryName,bc.BlogNewsCategoryID, n.BlogNewsHot,n.BlogNewsID,n.BlogNewsImg,n.CreateDate,n.CreateUser,n.UpdateDate,n.UpdateUser,n.Enable,nl.BlogNewsTitle,la.FlagImage,n.ViewCount from BlogNews n join BlogNewsLang nl on n.BlogNewsID = nl.BlogNewsID join BlogNewsCategory bc on n.BlogNewsCategoryID = bc.BlogNewsCategoryID join BlogNewsCategoryLang bcl on bc.BlogNewsCategoryID = bcl.BlogNewsCategoryID join [Language] la on nl.LangID = la.ID where nl.LangID = " + langid + " and la.ID = " + langid + " and bcl.LangID = " + langid + "");
        }

        // xóa blog news

        public int DeleteBlogNews(int BlogNewsID)
        {
            return db.exeUpdate("Delete from BlogNews where BlogNewsID = " + BlogNewsID + "");
        }
        public int DeleteBlogNewsLang(int BlogNewsID)
        {
            return db.exeUpdate("Delete from BlogNewsLang where BlogNewsID = " + BlogNewsID + "");
        }
    }
}
